package com.tbsoft.ehr.dao.attendance;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.Attendance;
import com.tbsoft.ehr.model.StaffInfo;
import com.tbsoft.ehr.util.ConfigProps;

public class AttendanceDaoImpl extends BaseDaoImpl<Attendance> implements
		AttendanceDao {

	@Override
	public List<Attendance>  getYearLeaveByUserId(final String userId,final String startDate,final String endDate) {
		//获取年假字段的值
		final String njId = (String)ConfigProps.getContextProperty("ehr.public.attend.yearLeave");
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("from Attendance ad where ad.staff.userId=? and ad.adType=? and ad.adDate between ? and ? )");
				q.setString(0, userId);
				q.setString(1, njId);
				q.setString(2, startDate);
				q.setString(3, endDate);
				return q.list();
			}
		});
	}

	@Override
	public List<Attendance> getAbsenceByCondton(List<StaffInfo> staffLst,
			String adTimeFrom, String adTimeTo, final int startIndex, final int count) {
		StringBuilder sb = new StringBuilder("from Attendance ad where ad.status<>'D' ");
		StringBuilder sb2 = new StringBuilder();
		if(staffLst!=null&&staffLst.size()>0) {
			sb2.append("and ad.staff.userId in(");
			for(StaffInfo si:staffLst){
				sb2.append("'").append(si.getUserId()).append("',");
			}
			int i = sb2.lastIndexOf(",");
			sb2.replace(i, i+1, ") ");
		}
		sb.append(sb2.toString());
		if (adTimeFrom != null && adTimeTo != null) {
			sb.append("and ad.adDate between '").append(adTimeFrom).append(
					"' and '").append(adTimeTo).append("' ");
		}
		sb.append(" order by ad.staff,ad.adDate asc ");
		final String queryStr = sb.toString();
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery(queryStr).setFirstResult(startIndex).setMaxResults(count).list();
			}
		}); 
	}

	@Override
	public int getAbsenceCount(List<StaffInfo> staffLst, String adTimeFrom,
			String adTimeTo) {
		StringBuilder sb = new StringBuilder("select count(*) from Attendance ad where ad.status<>'D' ");
		StringBuilder sb2 = new StringBuilder();
		if(staffLst!=null&&staffLst.size()>0) {
			sb2.append("and ad.staff.userId in(");
			for(StaffInfo si:staffLst){
				sb2.append("'").append(si.getUserId()).append("',");
			}
			int i = sb2.lastIndexOf(",");
			sb2.replace(i, i+1, ") ");
		}
		sb.append(sb2.toString());
		if (adTimeFrom != null && adTimeTo != null) {
			sb.append("and ad.adDate between '").append(adTimeFrom).append(
					"' and '").append(adTimeTo).append("' ");
		}
		final String queryStr = sb.toString();
		Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery(queryStr).uniqueResult();
			}
		});
		return count.intValue();
	}

	@Override
	public Attendance getAttendanceById(String id) {		
		return (Attendance)getHibernateTemplate().get(Attendance.class, Integer.valueOf(id));
	}

	@Override
	public void updateAbsence(final Attendance ad) {
		getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("update Attendance ad set ad.adType=?, ad.adDate=?, ad.hours=?,ad.remark=? where ad.id=?");
				q.setString(0, ad.getAdType());
				q.setDate(1, ad.getAdDate());
				q.setBigDecimal(2, ad.getHours());
				q.setString(3, ad.getRemark());
				q.setInteger(4, ad.getId());
				return q.executeUpdate();
			}
		});
	}

	@Override
	public boolean hasAbsenceInDate(final String userId,final Date d) {
		Long count = (Long)getHibernateTemplate().execute(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("select count(*) from Attendance ad where ad.staff.userId=? and ad.adDate=?");
				q.setString(0, userId);
				q.setDate(1, d);
				return q.uniqueResult();
			}
		});
		if(count.intValue()!=0){
			return true;
		}
		return false;
	}

	@Override
	public List<Attendance> getDaysOffByStaffId(final String staffId) {
		//获取调休假字段的值
		final String txjId = (String)ConfigProps.getContextProperty("ehr.public.attend.daysOff");
		return getHibernateTemplate().executeFind(new HibernateCallback() {		
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query q = session.createQuery("from Attendance ad where ad.staff=? and ad.adType=? )");
				q.setString(0, staffId);
				q.setString(1, txjId);
				return q.list();
			}
		});
	}
}
